import threading

import pymysql

from env import envParams


# 连接池类
class ConnectionPool:
    def __init__(self, pool_size=5):
        self.pool_size = pool_size
        self.pool = []
        self.lock = threading.Lock()

    def create_connections(self):
        for _ in range(self.pool_size):
            conn = pymysql.connect(
                host=envParams.MYSQL_HOST,
                user=envParams.MYSQL_USER,
                passwd=envParams.MYSQL_PASSWORD,
                db=envParams.MYSQL_DB,
                port=envParams.MYSQL_PORT,
                charset='utf8mb4'
            )
            self.pool.append(conn)

    def get_connection(self):
        with self.lock:
            if not self.pool:
                self.create_connections()
            return self.pool.pop()

    def return_connection(self, conn):
        with self.lock:
            self.pool.append(conn)


# 创建连接池
pool = ConnectionPool()


# 测试方法
def worker(pool):
    while True:
        conn = pool.get_connection()
        try:
            with conn.cursor() as cursor:
                # 执行数据库操作
                cursor.execute("YOUR SQL COMMAND")
                # 处理结果
                results = cursor.fetchall()
                print(results)
        finally:
            pool.return_connection(conn)
